package jwebtest.poi;

import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

/**
 * 导出excel
 * 
 * @author yuanqiyong
 *
 */
public class ExportExcelUtil {
	// 当前行
	private static ThreadLocal<Integer> cell_row = new ThreadLocal<Integer>();
	// 当前行样式
	private static ThreadLocal<CellStyle> defCellStyle = new ThreadLocal<CellStyle>();

	/**
	 * 设置表名
	 */
	public static void setTableName(HttpServletResponse response, String tableName) {
		try {
			response.reset(); // 清空缓存区
			String excelName = tableName + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + ".xls";
			excelName = new String(excelName.getBytes("gb2312"), "ISO-8859-1");
			response.setHeader("Content-Disposition", "attachment;fileName=" + excelName); // 设置文件头
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 设置Excel表列名
	 */
	public static void setCellHead(Sheet sheet, Workbook wb, String[] cellNames) throws Exception {
		Cell cell;
		Font font = wb.createFont();
		font.setFontName("宋体");
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
		CellStyle cellStyle = wb.createCellStyle(); // 创建一个样式
		cellStyle.setFont(font);
		Row row = sheet.createRow(0);
		sheet.setDefaultColumnWidth((short) 20);
		for (int i = 0; i < cellNames.length; i++) {
			cell = row.createCell(i);
			cell.setCellType(Cell.CELL_TYPE_STRING);
			cell.setCellValue((cellNames[i]));
			cell.setCellStyle(cellStyle);
		}
		setCell_row(0);
	}

	/**
	 * 填充数据（多行）
	 */
	public static void setCellData(Sheet sheet, List<String[]> list) throws Exception {
		int[] type = new int[list.get(0).length];
		setCellData(sheet, list, type);
	}

	/**
	 * 填充数据（多行）
	 * 
	 * @param int[] type=[1:数字类型,0:字符类型]
	 */
	public static void setCellData(Sheet sheet, List<String[]> list, int[] type) throws Exception {
		for (int i = 0; i < list.size(); i++) {
			String[] arr = list.get(i);
			setCellDataRow(sheet, arr, type);
		}
	}

	/**
	 * 填充数据（一行）
	 */
	public static void setCellDataRow(Sheet sheet, String[] arr) throws Exception {
		int[] type = new int[arr.length];
		setCellDataRow(sheet, arr, type);
	}

	/**
	 * 填充数据（一行）
	 * 
	 * @param int[] type=[1:数字类型,0:字符类型]
	 */
	public static void setCellDataRow(Sheet sheet, String[] arr, int[] type) throws Exception {
		Cell cell;
		Row row = sheet.createRow(getCell_row() + 1);// 建立新行
		for (int j = 0; j < arr.length; j++) {
			cell = row.createCell(j);
			if (type[j] == 1) {
				cell.setCellType(Cell.CELL_TYPE_NUMERIC);
				BigDecimal bdvalue = null;
				try {
					bdvalue = new BigDecimal(arr[j]);
				} catch (Exception e) {
					bdvalue = new BigDecimal("0");
				}
				bdvalue = bdvalue.setScale(2, BigDecimal.ROUND_HALF_UP);
				cell.setCellValue(bdvalue.doubleValue());
			} else if (type[j] == 0) {
				cell.setCellType(Cell.CELL_TYPE_STRING);
				cell.setCellValue((arr[j]));
			}
			if (defCellStyle.get() != null)
				cell.setCellStyle(defCellStyle.get());
		}
		setCell_row(getCell_row() + 1);
	}

	/**
	 * 设置表格样式模板
	 */
	public static void setStyleModel(CellStyle style) {
		defCellStyle.set(style);
	}

	/**
	 * 一行代码 执行导出Excel
	 * 
	 * @param wb
	 * @param excelTitle
	 *            标题
	 * @param cellNames
	 *            列名
	 * @param cellTypeArr
	 *            列类型[1:数字类型,0:字符类型]
	 * @param dataList
	 *            导出数据
	 * @param rowData
	 *            可以默认null,最后添加的一行数据[一般为签名，汇总]
	 * @param rowCellTypes
	 *            可以默认null，最后一行数据的数据类型。
	 * @param totalFlag
	 *            可以默认null,在最后一条数据前是否添加一行空格，
	 * @param response
	 *            HttpServletResponse 输出
	 * @throws Exception
	 */
	public static void exportExcelData(Workbook wb, String excelTitle, String[] cellNames, int[] cellTypeArr, List<String[]> dataList,
			String[] rowData, int[] rowCellTypes, boolean totalFlag, HttpServletResponse response) throws Exception {
		Sheet sheet = wb.createSheet(excelTitle);// 建立新的sheet对象
		setTableName(response, excelTitle);// 设置文件类型
		setCellHead(sheet, wb, cellNames);// 创建列头
		setCellData(sheet, dataList, cellTypeArr); // 创建列的数据
		if (rowData != null && rowData.length > 0) {
			if (totalFlag)
				setCellDataRow(sheet, new String[] { "" }, new int[] { 1 });
			setCellDataRow(sheet, rowData, rowCellTypes);
		}
		wb.write(response.getOutputStream());// 导出excel
	}

	/**
	 * 强制获取String类型的值。世界顿时清静了。
	 * 
	 * @param cell
	 * @return
	 */
	public static String getCellValueString(Cell cell) {
		if (cell == null)
			return "";
		if (cell.getCellType() != Cell.CELL_TYPE_STRING)
			cell.setCellType(Cell.CELL_TYPE_STRING);
		return cell.getStringCellValue() + "";
	}

	public static int getCell_row() {
		return cell_row.get() == null ? 0 : cell_row.get();
	}

	public static void setCell_row(int cellRow) {
		cell_row.set(cellRow);
	}

	/**************************************** 导出使用方法 *************************************/
	public static void main(String[] args) {

		// // 导出参数
		// HSSFWorkbook wb = new HSSFWorkbook();// 建立新HSSFWorkbook对象
		// HSSFRow row = null;
		// HSSFSheet sheet = wb.createSheet("测试excel");// 建立新的sheet对象
		// ExportExcelUtil.setTableName(response, "测试excel");
		// String[] cellNames = { "姓名", "性别", "年龄", "成绩" };
		// int[] cellTypeArr = { 1, 1, 1, 0 };// 1为字符串
		// // 0为数值
		// ExportExcelUtil.setCellHead(sheet, row, cellNames);// 创建列头
		// List<String[]> list = new ArrayList<String[]>();
		// String[] data = { String.valueOf("张三"), String.valueOf("男"),
		// String.valueOf("18"), String.valueOf(95.5) };
		// list.add(data);
		// ExportExcelUtil.setCellData(sheet, row, list, cellTypeArr); // 创建列的数据
		// wb.write(response.getOutputStream());// 导出excel

	}

}
